﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using TrainingWorkshop.DAL_EF;

namespace DAL_EF
{
    /// <summary>
    /// Author: Sissi Zeng
    /// Date:2012-08-12 21:50
    /// This class is used to handle data from  CourseList in database
    /// </summary>
    public class CourseListView
    {


        /// <summary>
        /// select correct records in CourseList View by specific condition
        /// </summary>
        /// <param name="condition"></param>
        /// <returns>List of Course info</returns>
        public List<TrainingWorkshop.Model.CourseList> SearchCourse(TrainingWorkshop.Model.Condition condition) {

            using (var db = new projectbEntities()) {
              //  var query= db.CourseLists.AsQueryable();

                //join tables
                IQueryable<TrainingWorkshop.Model.CourseList> query = from c in db.Courses
                            join cd in db.CourseDetails
                            on c.CourseId equals cd.CourseId
                            join l in db.Levels
                            on c.LevelId equals l.LevelId
                            join t in db.Teachers
                            on c.TeacherId equals t.TeacherId
                            
                            group c by new {
                                c.CourseId,
                                c.CourseName,
                                c.CourseStatus,
                                t.TeacherName,
                                l.LevelName

                            } into cl
                           
                            select new TrainingWorkshop.Model.CourseList {
                                CourseId=cl.Key.CourseId,
                                CourseName=cl.Key.CourseName,
                                CourseStatus=cl.Key.CourseStatus,
                                TeacherName=cl.Key.TeacherName,
                                LevelName=cl.Key.LevelName,
                                StudentNumber=cl.Count()

                            };
                
               // judge where condition


                if (condition.CourseStatus >= 0) {
                    bool status = false;
                    if (condition.CourseStatus == 1) {
                        status = true;
                    }
                    query = query.Where(c => c.CourseStatus == status);
                }
                if (!string.IsNullOrWhiteSpace(condition.TeacherName)) {
                    query = query.Where(c=>c.TeacherName==condition.TeacherName);
                    
                }
                if (!string.IsNullOrWhiteSpace(condition.LevelName)) {
                    query = query.Where(c => c.LevelName == condition.LevelName);
                }
                if (!string.IsNullOrWhiteSpace(condition.CourseName)) {
                    query = query.Where(c => c.CourseName.Contains(condition.CourseName));
                }

                //judge sorting and handle

                //    if (condition.OrderDire == "ASC")
                //    {
                //       if(condition.SortExpression=="CourseStatus")  query=query.OrderBy(c => c.CourseStatus);
                //       if (condition.SortExpression == "CourseId") query=query.OrderBy(c => c.CourseId);
                //       if (condition.SortExpression == "CourseName") query=query.OrderBy(c => c.CourseName);
                //       if (condition.SortExpression == "TeacherName") query=query.OrderBy(c => c.TeacherName);
                //       if (condition.SortExpression == "LevelName") query=query.OrderBy(c => c.LevelName);
                //    }
                //    else if (condition.OrderDire == "Desc")
                //    {
                //        if (condition.SortExpression == "CourseStatus") query = query.OrderByDescending(c => c.CourseStatus);
                //        if (condition.SortExpression == "CourseId") query = query.OrderByDescending(c => c.CourseId);
                //        if (condition.SortExpression == "CourseName") query = query.OrderByDescending(c => c.CourseName);
                //        if (condition.SortExpression == "TeacherName") query = query.OrderByDescending(c => c.TeacherName);
                //        if (condition.SortExpression == "LevelName") query = query.OrderByDescending(c => c.LevelName);
                //    }
             
                return query.ToList();
            }



           
        }
    }
}
